Am I on the only in the technical community that is completely annoyed by SQL’s INSERT statement syntax? Especially with long INSERT queries.

Why in the world do we have to have the column and its value separated.

Imagine this table, which is an overly simplified example only version of a Person table.

Please not that this table only has 10 columns. I am working with tables with three of four times as many columns. Also these columns are pretty simple. Many columns have name like PK_D or other cryptic titles.

So wouldn’t it be nice to have a syntax that more easily connects the column and the value?

Lets look at the insert syntax:

INSERT INTO Person (FirstName, MiddleName, LastName, Birthday, UserName, Password, PhoneNumber, Address, SignupDate, LastUpdated, LastPasswordChange)
VALUES ('John', 'J.', 'Doe', '3/27/1977', 'jdoe', 'p@ssw0rd!, '555-555-5555, '1234 ABCD Street SomeCity, Utah 81234', '12/1/2012', '3/20/2013', '3/13/2013')

Ok, so you can start to see how even with these easy and well-known columns, it starts to get confusing as to which order you need to enter data. Also, you have to scroll right to left a lot. Also, in whichever order you type your column names, you have to enter you data.

Even with every value on its own line, this is still not very clear.

INSERT INTO Person (
FirstName, 
MiddleName, 
LastName, 
Birthday, 
UserName, 
Password, 
PhoneNumber, 
Address, 
SignupDate, 
LastUpdated, 
LastPasswordChange)
VALUES (
'John', 
'J.', 
'Doe', 
'3/27/1977', 
'jdoe', 
'p@ssw0rd!, 
'555-555-5555, 
'1234 ABCD Street SomeCity, Utah 81234', 
'12/1/2012', 
'3/20/2013', 
'3/13/2013')

Now it is a lot of lines, and you have just swapped extensive left and right scrolling to up and down scrolling. Remember, this is a small table. Some tables have many times more columns.

Of course you can get around some of this using default values and allowing NULLs so you don’t have to insert values for those columns. But if you have 30+ required columns, which the database I am working with has, then that doesn’t help. No, I am not in control of the database or I would likely change it.

Why is the syntax not something that is order insignificant?

INSERT INTO Person
FirstName='John', MiddleName='J.', LastName='Doe', Birthday='3/27/1977'
UserName='jdoe', Password='p@ssw0rd!, PhoneNumber='555-555-5555,
Address='1234 ABCD Street SomeCity, Utah 81234', SignupDate='12/1/2012',
LastUpdate='3/20/2013', LastPasswordChange='3/13/2013'

Or all on its own line:

INSERT INTO Person
FirstName='John', 
MiddleName='J.', 
LastName='Doe', 
Birthday='3/27/1977'
UserName='jdoe', 
Password='p@ssw0rd!, 
PhoneNumber='555-555-5555,
Address='1234 ABCD Street SomeCity, Utah 81234', 
SignupDate='12/1/2012',
LastUpdate='3/20/2013', 
LastPasswordChange='3/13/2013'

Is it just me who thinks that the standard property=value syntax is a quite a bit more readable and a lot more writable?

I am sure there are all kinds of reasons why this syntax isn’t used. I am not saying property=value syntax is a fix to the horrible INSERT syntax. I am just saying that the INSERT syntax is horrible and I would recommend that future version of SQL provide and more readable a more writable alternative.